package com.trigsoft.applist;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashSet;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.os.Environment;
import android.util.Log;

public class DBHelper
{
	private static class DatabaseHelper extends SQLiteOpenHelper
	{
		Context mContext;

		DatabaseHelper(Context context)
		{
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			mContext = context;
		}

		@Override
		public void onCreate(SQLiteDatabase db)
		{
			db.execSQL(CREATE_APPS_TABLE);
			createCatDB(mContext);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
		{
			//Don't upgrade db from 1.3.6 when db version is 3 as it may lose the app itself in the DB 
			if (oldVersion < 7)
			{
				OLD_VERSION = oldVersion;
				toUpgrade = true;
			}
		}

	}

	public static final String KEY_ID = "_id";
	public static final String KEY_NAME = "name";
	public static final String KEY_PKG = "packageName";
	public static final String KEY_PINYIN = "pinyin";
	public static final String KEY_ICON = "icon";
	public static final String KEY_LABEL = "label";
	public static final String KEY_FIRST = "first";
	public static final String KEY_INRECENT = "inrecent";
	public static final String KEY_INLIST = "inlist";
	public static final String KEY_COUNT = "count";
	public static final String KEY_DEFAULT = "isdefault";
	public static final String KEY_CAT = "category";
	public static final String KEY_CAT_CAT = "cat";

	public static final String KEY_PKG_CAT = "pkg";
	private static DatabaseHelper mDbHelper = null;

	private static SQLiteDatabase mDb;
	private static final String DATABASE_NAME = "APPLIST";
	private static final int DATABASE_VERSION = 7;
	public static int OLD_VERSION;

	public static boolean toUpgrade = false;
	private static final String APPS_TABLE = "application";

	private static final String CATS_TABLE = "catdb.category";

	private static final String CREATE_APPS_TABLE = "create table " + APPS_TABLE + " (" + KEY_ID
			+ " integer primary key autoincrement, " + KEY_ICON + " blob not null, " + KEY_NAME
			+ " text not null unique, " + KEY_PKG + " text not null, " + KEY_PINYIN + " text not null, " + KEY_LABEL
			+ " text not null, " + KEY_FIRST + " text not null, " + KEY_INRECENT + " integer default 1, " + KEY_INLIST
			+ " integer default 1, " + KEY_COUNT + " integer default 0, " + KEY_DEFAULT + " integer default 1, "
			+ KEY_CAT + " text default other);";
	public static final int in = 1; // The app will be shown in the list

	public static final int notIn = 0; // The app will not be shown in the list
	private static final String DB_PATH = "/data" + Environment.getDataDirectory().getAbsolutePath()
			+ "/com.trigsoft.applist/databases";
	private static String dbfile = DB_PATH + "/category.db";

	private final static int BUFFER_SIZE = 400000;

	public static void attachCat(Context context)
	{
		createCatDB(context);
		mDbHelper.getWritableDatabase().execSQL("attach database ? as catdb", new String[] {dbfile});
	}

	public static void close()
	{
		mDbHelper.close();
	}

	public static void createAppEntry(App app)
	{
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		app.getIcon().compress(Bitmap.CompressFormat.PNG, 100, out);
		try
		{
			out.flush();
			out.close();
		} catch (IOException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		ContentValues cv = new ContentValues();
		cv.put(KEY_ICON, out.toByteArray());
		cv.put(KEY_NAME, app.getName());
		cv.put(KEY_PKG, app.getPkgName());
		cv.put(KEY_PINYIN, app.getPinyin());
		cv.put(KEY_LABEL, app.getLabel());
		char first = app.getPinyin().charAt(0);
		if (Character.isDigit(first))
			cv.put(KEY_FIRST, "#");
		else
			cv.put(KEY_FIRST, Character.toString(first));
		cv.put(KEY_INRECENT, in);
		cv.put(KEY_INLIST, in);
		cv.put(KEY_COUNT, 0);
		cv.put(KEY_DEFAULT, app.getDef());
		//cv.put(KEY_CAT, getCat(app.getPkgName(), wifi));
		Log.d("test", Long.toString(mDbHelper.getWritableDatabase().insert(APPS_TABLE, null, cv)));
	}

	private static void createCatDB(Context context)
	{
		if (!new File(dbfile).exists())
		{
			InputStream is = context.getResources().openRawResource(R.raw.db);
			FileOutputStream fos;
			try
			{
				fos = new FileOutputStream(dbfile);

				byte[] buffer = new byte[BUFFER_SIZE];
				int count = 0;
				while ((count = is.read(buffer)) > 0)
				{
					fos.write(buffer, 0, count);
				}
				fos.close();
				is.close();
			} catch (FileNotFoundException e)
			{
				Log.e("Database", "File not found");
				e.printStackTrace();
			} catch (IOException e)
			{
				Log.e("Database", "IO exception");
				e.printStackTrace();
			}
		}
	}

	public static void createOrUpdate(App app)
	{
		if (updateAppEntry(app) == 0)
			createAppEntry(app);
	}

	public static void deleteAppEntry(String pkg)
	{
		mDbHelper.getWritableDatabase().delete(APPS_TABLE, KEY_PKG + "=?", new String[] {pkg});
	}

	public static void deleteAppEntryByName(String name)
	{
		mDbHelper.getWritableDatabase().delete(APPS_TABLE, KEY_NAME + "=?", new String[] {name});
	}

	public static void detachCat()
	{
		mDbHelper.getWritableDatabase().execSQL("detach database catdb");
	}

	public static String getCat(String pkgName)
	{
		// TODO Auto-generated method stub
		Cursor cur = mDbHelper.getReadableDatabase().query(CATS_TABLE, new String[] {KEY_CAT_CAT}, KEY_PKG_CAT + "=?",
				new String[] {pkgName}, null, null, null, "1");
		String result = "";
		if (cur.moveToFirst() && cur.getCount() > 0)
		{
			boolean first = true;
			do
			{
				if (!first)
					result += ",";
				else
					first = false;
				result += cur.getString(cur.getColumnIndex(KEY_CAT_CAT));
			} while (cur.moveToNext());

		} else
		{
			result = "other";
		}
		return result;
	}

	public static String getCurrentCat(String pkgName)
	{
		// TODO Auto-generated method stub
		Cursor cur = mDbHelper.getReadableDatabase().query(APPS_TABLE, new String[] {KEY_CAT}, KEY_PKG + "=?",
				new String[] {pkgName}, null, null, null, "1");
		String result = "";
		if (cur.moveToNext())
			result = cur.getString(cur.getColumnIndex(DBHelper.KEY_CAT));

		return result;
	}

	public static LinkedHashSet<String> getCats()
	{
		Cursor cur = mDbHelper.getReadableDatabase().query(APPS_TABLE, new String[] {"distinct " + KEY_CAT}, null,
				null, null, null, null);
		if (cur.moveToFirst())
		{
			LinkedHashSet<String> result = new LinkedHashSet<String>(cur.getCount());
			do
			{
				String catField = cur.getString(cur.getColumnIndex(DBHelper.KEY_CAT));
				String[] cats = catField.split(",");
				for (String s : cats)
					result.add(s);
			} while (cur.moveToNext());
			return result;
		}
		return null;
	}

	public static App getFirstAppFromDB() throws SQLException
	{
		Cursor cur = mDbHelper.getReadableDatabase().query(true, APPS_TABLE,
				new String[] {KEY_ID, KEY_ICON, KEY_NAME, KEY_PKG, KEY_PINYIN, KEY_LABEL, KEY_DEFAULT}, null, null,
				null, null, null, null);
		if (cur.moveToFirst())
		{
			byte[] blob = cur.getBlob(cur.getColumnIndex(KEY_ICON));
			Bitmap bmp = BitmapFactory.decodeByteArray(blob, 0, blob.length);
			String name = cur.getString(cur.getColumnIndex(KEY_NAME));
			String pkgName = cur.getString(cur.getColumnIndex(KEY_PKG));
			String pinyin = cur.getString(cur.getColumnIndex(KEY_PINYIN));
			String label = cur.getString(cur.getColumnIndex(KEY_LABEL));
			int def = cur.getInt(cur.getColumnIndex(KEY_DEFAULT));
			cur.close();
			return new App(bmp, name, pkgName, pinyin, label, def);
		}
		cur.close();
		return null;
	}

	public static ArrayList<String> getPkgNotCat()
	{
		// TODO Auto-generated method stub
		Cursor cur = mDbHelper.getReadableDatabase().query(APPS_TABLE, new String[] {"distinct " + KEY_PKG},
				KEY_CAT + "=?", new String[] {"other"}, null, null, null);
		if (cur.moveToFirst())
		{
			ArrayList<String> result = new ArrayList<String>(cur.getCount());
			do
			{
				result.add(cur.getString(cur.getColumnIndex(KEY_PKG)));
			} while (cur.moveToNext());
			return result;
		}
		return null;
	}

	public static void init(Context ctx) throws SQLException
	{
		if (mDbHelper == null)
			mDbHelper = new DatabaseHelper(ctx);
	}

	public static boolean isEmpty() throws SQLException
	{
		//mDb = mDbHelper.getWritableDatabase();
		boolean result;
		Cursor cur = mDbHelper.getReadableDatabase().query(true, APPS_TABLE, new String[] {KEY_ID}, null, null, null,
				null, null, null);
		if (cur.getCount() == 0)
			result = true;
		else
			result = false;
		cur.close();

		return result;
	}

	public static Cursor queryByAlpha(String alpha) throws SQLException
	{

		Cursor cur = mDbHelper.getReadableDatabase().query(true, APPS_TABLE,
				new String[] {KEY_ID, KEY_ICON, KEY_NAME, KEY_PKG, KEY_PINYIN, KEY_LABEL, KEY_INRECENT, KEY_CAT},
				KEY_FIRST + "=? AND " + KEY_INLIST + "= 1", new String[] {alpha.toLowerCase()}, null, null, KEY_PINYIN,
				null);
		return cur;
	}

	public static Cursor queryByCat(String cat) throws SQLException
	{
		Cursor cur;
		if (cat.compareTo("sport") != 0)
			cur = mDbHelper.getReadableDatabase().query(true, APPS_TABLE,
					new String[] {KEY_ID, KEY_ICON, KEY_NAME, KEY_PKG, KEY_PINYIN, KEY_LABEL, KEY_INRECENT, KEY_CAT},
					KEY_INLIST + "= 1 AND " + KEY_CAT + " like '%" + cat + "%'", null, null, null, KEY_PINYIN, null);
		else
			cur = mDbHelper.getReadableDatabase().query(
					true,
					APPS_TABLE,
					new String[] {KEY_ID, KEY_ICON, KEY_NAME, KEY_PKG, KEY_PINYIN, KEY_LABEL, KEY_INRECENT, KEY_CAT},
					KEY_INLIST + "= 1 AND " + KEY_CAT + " like '" + cat + ",%' OR " + KEY_CAT + " like '%," + cat
							+ "%' OR " + KEY_CAT + " like '" + cat + "'", null, null, null, KEY_PINYIN, null);
		return cur;
	}

	public static Cursor queryByPKG(String pkg) throws SQLException
	{

		Cursor cur = mDbHelper.getReadableDatabase().query(true, APPS_TABLE, new String[] {KEY_NAME, KEY_LABEL},
				KEY_PKG + "='" + pkg + "'", null, null, null, null, null);
		return cur;
	}

	/*public static Cursor queryByAlpha(String alpha) throws SQLException
	{

		Cursor cur = mDbHelper.getWritableDatabase().query(true, APPS_TABLE,
				new String[] {KEY_ID, KEY_ICON, KEY_NAME, KEY_PKG, KEY_PINYIN, KEY_LABEL},
				KEY_FIRST + "='" + alpha.toLowerCase() + "'", null, null, null, KEY_PINYIN, null);
		if (cur.moveToFirst())
		{
			//Log.d("TEST", "OK");
			return cur;
		}
		return null;
	}*/

	public static Cursor queryByPKGName(String pkg, String name) throws SQLException
	{

		//Cursor cur = mDbHelper.getWritableDatabase().query(false, APPS_TABLE, new String[] {KEY_NAME},
		//		KEY_PKG + "=? AND " + KEY_NAME + "=?", new String[]{pkg, name}, null, null, null, null);
		Cursor cur = mDbHelper.getReadableDatabase().query(false, APPS_TABLE, new String[] {KEY_NAME},
				KEY_NAME + "='" + name + "'", null, null, null, null, null);
		return cur;
	}

	public static Cursor queryForExport() throws SQLException
	{
		Cursor cur = mDbHelper.getWritableDatabase().query(true, APPS_TABLE, new String[] {KEY_PKG, KEY_LABEL}, null,
				null, null, null, KEY_PINYIN, null);
		return cur;
	}

	public static Cursor queryIcon(String n) throws SQLException
	{
		Cursor cur = mDbHelper.getReadableDatabase().rawQuery(
				"SELECT icon FROM application WHERE label LIKE '" + n + "'", null);
		cur.moveToFirst(); //must have this
		return cur;
	}

	public static Cursor queryIconLable() throws SQLException
	{
		Cursor cur = mDbHelper.getReadableDatabase().query(true, APPS_TABLE, new String[] {KEY_LABEL, KEY_ICON}, null,
				null, null, null, KEY_PINYIN, null);
		return cur;
	}

	public static Cursor queryInCount() throws SQLException
	{
		Cursor cur = mDbHelper.getReadableDatabase().query(
				true,
				APPS_TABLE,
				new String[] {KEY_ID, KEY_ICON, KEY_NAME, KEY_PKG, KEY_PINYIN, KEY_LABEL, KEY_CAT,
						"sum(" + KEY_COUNT + ")as count"}, KEY_DEFAULT + "=?",
				new String[] {Integer.toString(App.YES)}, KEY_PKG, null, KEY_COUNT + " DESC", null);
		return cur;
	}

	public static Cursor queryInCountInRecent() throws SQLException
	{

		Cursor cur = mDbHelper.getReadableDatabase().query(true, APPS_TABLE,
				new String[] {KEY_ID, KEY_NAME, KEY_PKG, KEY_LABEL, KEY_COUNT, KEY_CAT},
				KEY_INRECENT + "= 1 AND " + KEY_COUNT + "!= 0", null, null, null, KEY_COUNT + " DESC", "10");
		return cur;
	}

	public static boolean queryInListByPKG(String name) throws SQLException
	{

		Cursor cur = mDbHelper.getReadableDatabase().query(true, APPS_TABLE, new String[] {KEY_INLIST},
				KEY_NAME + "=? AND " + KEY_INLIST + "=?", new String[] {name, "1"}, null, null, null, null);
		boolean result = false;
		if (cur.getCount() > 0)
			result = true;
		cur.close();
		return result;
	}

	public static boolean queryInRecentByPKG(String name) throws SQLException
	{

		Cursor cur = mDbHelper.getReadableDatabase().query(true, APPS_TABLE, new String[] {KEY_INRECENT},
				KEY_NAME + "=? AND " + KEY_INRECENT + "=?", new String[] {name, "1"}, null, null, null, "1");
		boolean result = false;
		if (cur.getCount() > 0)
			result = true;
		cur.close();
		return result;
	}

	public static Cursor queryNotInList() throws SQLException
	{
		Cursor cur = mDbHelper.getReadableDatabase().query(true, APPS_TABLE,
				new String[] {KEY_ID, KEY_ICON, KEY_NAME, KEY_PKG, KEY_PINYIN, KEY_LABEL},
				KEY_INLIST + " = '" + notIn + "'", null, null, null, KEY_PINYIN, null);
		//cur.moveToFirst();
		return cur;

	}

	public static Cursor queryNotInRecent() throws SQLException
	{
		Cursor cur = mDbHelper.getReadableDatabase().query(true, APPS_TABLE,
				new String[] {KEY_ID, KEY_ICON, KEY_NAME, KEY_PKG, KEY_PINYIN, KEY_LABEL},
				KEY_INRECENT + " = '" + notIn + "'", null, null, null, KEY_PINYIN, null);
		//cur.moveToFirst();
		return cur;
	}

	public static Cursor queryNotInRecentOrNotInListCat()
	{
		Cursor cur = mDbHelper.getReadableDatabase().query(true, APPS_TABLE,
				new String[] {KEY_NAME, KEY_INRECENT, KEY_INLIST, KEY_CAT}, null, null, null, null, null, null);
		return cur;
	}

	public static void Reset()
	{
		mDbHelper.onUpgrade(mDb, 1, 1);
	}

	public static int updateAppEntry(App app)
	{
		// TODO Auto-generated method stub
		Bitmap icon = app.getIcon();
		int size = icon.getWidth() * icon.getHeight() * 4;
		ByteArrayOutputStream out = new ByteArrayOutputStream(size);
		app.getIcon().compress(Bitmap.CompressFormat.PNG, 100, out);
		try
		{
			out.flush();
			out.close();
		} catch (IOException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		ContentValues cv = new ContentValues();
		cv.put(KEY_ICON, out.toByteArray());
		cv.put(KEY_NAME, app.getName());
		cv.put(KEY_PINYIN, app.getPinyin());
		cv.put(KEY_LABEL, app.getLabel());
		cv.put(KEY_DEFAULT, app.getDef());
		//cv.put(KEY_CAT, getCat(app.getPkgName(), wifi));

		char first = app.getPinyin().charAt(0);
		if (Character.isDigit(first))
			cv.put(KEY_FIRST, "#");
		else
			cv.put(KEY_FIRST, Character.toString(first));
		return mDbHelper.getWritableDatabase().update(APPS_TABLE, cv, KEY_NAME + "='" + app.getName() + "'", null);
	}

	public static void updateCat(String cat, String pkg)
	{
		// TODO Auto-generated method stub
		ContentValues cv = new ContentValues();
		cv.put(KEY_CAT, cat);
		mDbHelper.getWritableDatabase().update(APPS_TABLE, cv, KEY_PKG + "=?", new String[] {pkg});
	}

	public static void updateCount(String pkg)
	{
		SQLiteDatabase db = mDbHelper.getWritableDatabase();
		db.execSQL("UPDATE application SET count = count + 1 WHERE packageName=? AND isdefault=?", new String[] {pkg,
				Integer.toString(App.YES)});
	}

	public static void updateInList(int in, String name)
	{
		ContentValues cv = new ContentValues();
		cv.put(KEY_INLIST, in);
		mDbHelper.getWritableDatabase().update(APPS_TABLE, cv, KEY_NAME + "='" + name + "'", null);
	}

	public static void updateInlistInrecent(String n, Integer inlist, Integer inrecent)
	{
		// TODO Auto-generated method stub
		ContentValues cv = new ContentValues();
		cv.put(KEY_INLIST, inlist);
		cv.put(KEY_INRECENT, inrecent);
		mDbHelper.getWritableDatabase().update(APPS_TABLE, cv, KEY_NAME + "=?", new String[] {n});
	}

	public static String updateInRecent(int in, String pkg, String name)
	{
		ContentValues cv = new ContentValues();
		cv.put(KEY_INRECENT, in);
		if (mDbHelper.getWritableDatabase().update(APPS_TABLE, cv, KEY_NAME + "='" + name + "'", null) > 0)
			return name;
		else
		{
			mDbHelper.getWritableDatabase().update(APPS_TABLE, cv, KEY_PKG + "='" + pkg + "'", null);
			return "DEFAULT";
		}
	}

	public static void updateInRecent(int in, String name)
	{
		ContentValues cv = new ContentValues();
		cv.put(KEY_INRECENT, in);
		mDbHelper.getWritableDatabase().update(APPS_TABLE, cv, KEY_NAME + "='" + name + "'", null);
	}

	public static void upgrade(Context context)
	{
		SQLiteDatabase db = mDbHelper.getWritableDatabase();
		if (OLD_VERSION < 6)
		{
			db.execSQL("ALTER TABLE application RENAME TO tmp");
			db.execSQL(CREATE_APPS_TABLE);

			if (OLD_VERSION == 1)
				db.execSQL("INSERT INTO application(icon, name, packageName, pinyin, label, first) "
						+ "SELECT icon, name, packageName, pinyin, label, first FROM tmp;");
			if (OLD_VERSION >= 2)
				db.execSQL("INSERT INTO application(icon, name, packageName, pinyin, label, first, inrecent, inlist) "
						+ "SELECT icon, name, packageName, pinyin, label, first, inrecent, inlist FROM tmp;");

			db.execSQL("DROP TABLE tmp");

			Cursor cursor = db.query(APPS_TABLE, new String[] {KEY_PKG, KEY_NAME}, KEY_INRECENT + "=?",
					new String[] {Integer.toString(notIn)}, null, null, null);
			NotInRecent nir = new NotInRecent(context);
			while (cursor.moveToNext())
			{
				nir.add(cursor.getString(cursor.getColumnIndex(KEY_PKG)), cursor.getString(cursor
						.getColumnIndex(KEY_NAME)));
			}
			nir.save(context);
		}

		if (OLD_VERSION == 6)
		{
			//db.execSQL("update application set category = 'personalization' where category = 'personalizations';");
			ContentValues cv = new ContentValues();
			cv.put(KEY_CAT, "game");
			db.update(APPS_TABLE, cv, KEY_CAT + "=?", new String[] {"games"});

			cv.clear();
			cv.put(KEY_CAT, "personalization");
			db.update(APPS_TABLE, cv, KEY_CAT + "=?", new String[] {"personalizations"});

			cv.clear();
			cv.put(KEY_CAT, "book");
			db.update(APPS_TABLE, cv, KEY_CAT + "=?", new String[] {"books"});

			cv.clear();
			cv.put(KEY_CAT, "business");
			db.update(APPS_TABLE, cv, KEY_CAT + "=?", new String[] {"businesses"});

			cv.clear();
			cv.put(KEY_CAT, "education");
			db.update(APPS_TABLE, cv, KEY_CAT + "=?", new String[] {"educations"});

			cv.clear();
			cv.put(KEY_CAT, "finace");
			db.update(APPS_TABLE, cv, KEY_CAT + "=?", new String[] {"finaces"});

			cv.clear();
			cv.put(KEY_CAT, "business");
			db.update(APPS_TABLE, cv, KEY_CAT + "=?", new String[] {"businesses"});

			cv.clear();
			cv.put(KEY_CAT, "communication");
			db.update(APPS_TABLE, cv, KEY_CAT + "=?", new String[] {"communications"});

			cv.clear();
			cv.put(KEY_CAT, "photo");
			db.update(APPS_TABLE, cv, KEY_CAT + "=?", new String[] {"photos"});

			cv.clear();
			cv.put(KEY_CAT, "sport");
			db.update(APPS_TABLE, cv, KEY_CAT + "=?", new String[] {"sports"});

			cv.clear();
			cv.put(KEY_CAT, "tool");
			db.update(APPS_TABLE, cv, KEY_CAT + "=?", new String[] {"tools"});

			cv.clear();
			cv.put(KEY_CAT, "transportation");
			db.update(APPS_TABLE, cv, KEY_CAT + "=?", new String[] {"transportations"});

			cv.clear();
			cv.put(KEY_CAT, "shopping");
			db.update(APPS_TABLE, cv, KEY_CAT + "=?", new String[] {"shoppings"});

			cv.clear();
			cv.put(KEY_CAT, "lifestyle");
			db.update(APPS_TABLE, cv, KEY_CAT + "=?", new String[] {"lifestyles"});
		}
		toUpgrade = false;
	}

	public static void updateInlistInrecentCat(String n, Integer inlist, Integer inrecent, String cat)
	{
		// TODO Auto-generated method stub
		ContentValues cv = new ContentValues();
		cv.put(KEY_INLIST, inlist);
		cv.put(KEY_INRECENT, inrecent);
		cv.put(KEY_CAT, cat);
		mDbHelper.getWritableDatabase().update(APPS_TABLE, cv, KEY_NAME + "=?", new String[] {n});

	}

}
